/**
 * Book数据模型模块
 */

//导入Node.js文件模块
const fs = require("fs");
//导入SQLite3模块   verbose() 方法会输出更详细的日志信息，例如 SQL 语句或执行结果等
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");
const { resolve } = require("path");
const { off } = require("process");

/**
 * BookDB构造函数
 * @constructs
 */
function BookDB(){
    this.dbFile = config.dbFile;
    this.instance = null; //BookDB数据库实例
    this.db = null; //SQLite3实例
}
/**
 * 创建Book对象
 */
BookDB.getInstance = function(){
    if(!this.instance){
        this.instance = new BookDB();
    }
    return this.instance;
};

/**
 * 连接数据库
 * @returns {Promise}
 */
BookDB.prototype.connect = function(){
    return new Promise((resolve,reject) => {
        this.db = new sqlite3.Database(this.dbFile,(err) => {
            if(err){
                reject(err)
            }else{
                resolve("connect ok")
            }
        })
    })
}

/**
 * 关闭数据库
 * @returns {Promise}
 */
BookDB.prototype.close = function(){
    return new Promise((resolve,reject) => {
        this.db.close((err) => {
            if(err){
                reject(err)
            }else{
                resolve("close ok")
            }
        })
    })
}

/**
 * 获取指定ID的书籍信息
 * @param {Number} bookId 书籍ID
 * @returns {Promise}
 */
BookDB.prototype.find = function(bookId){
    return new Promise((resolve,reject) => {
        let sql = "select * from books where id=?";
        let params = [bookId];
        this.db.get(sql,params,function(err,result) {
            if(err){
                reject(err);
            }else{
                resolve(result);
            }
        })
    })
}

/**
 * 获取书籍信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns {Promise}
 */
BookDB.prototype.findAll = function(limit = -1,offset = -1){
    return new Promise((resolve,reject) => {
        let sql = "";
        let params = [];
        if(limit === -1){
            sql = "select * from books order by id";
        }else if(offset === -1){
            sql = "select * from books order by id limit ?";
            params[0] = limit;
        }else{
            sql = "select * from books order by id limit ? offset ?";
            params[0] = limit;
            params[1] = offset;
        }
        // 一条用get，所有用all
        this.db.all(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                resolve(result);
            }
        })
    })
}

/**
 * 新增书籍
 * @param {Object} book 书籍数据
 * @returns {Promise}
 */
BookDB.prototype.add = function(book){
    return new Promise((resolve,reject) => {
        let sql = `insert into books (
            title, pic, local_pic, author, publisher, producer,
            subtitle, originalTitle, translator, pubdate, pages, price, 
            binding, series, isbn, intro, doubanId, created_time, 
            updated_time
        ) values (
            ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?,
            ?
        );`;
        let params = [book.title,
            book.pic,
            book.local_pic,
            book.author,
            book.publisher,
            book.producer,
            book.subtitle,
            book.originalTitle,
            book.translator,
            book.pubdate,
            book.pages,
            book.price,
            book.binding,
            book.series,
            book.isbn,
            book.intro,
            book.doubanId,
            book.created_time,
            book.updated_time,];
        this.db.run(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                resolve(this.lastID); //插入的数据的自增ID
            }
        })
    })
}

/**
 * 修改书籍
 * @param {Object} book 书籍数据
 * @returns {Promise}
 */
BookDB.prototype.update = function(book){
    return new Promise((resolve,reject) => {
        let sql = `update books set
            title = ?, pic = ?, local_pic = ?, author = ?, 
            publisher = ?, producer = ?, subtitle = ?, originalTitle = ?, 
            translator = ?, pubdate = ?, pages = ?, price = ?, 
            binding = ?, series = ?, isbn = ?, intro = ?, 
            doubanId = ?, updated_time = ?
            WHERE id = ?
        ;`;
        let params = [book.title,
            book.pic,
            book.localPic,
            book.author,
            book.publisher,
            book.producer,
            book.subtitle,
            book.originalTitle,
            book.translator,
            book.pubdate,
            book.pages,
            book.price,
            book.binding,
            book.series,
            book.isbn,
            book.intro,
            book.doubanId,
            book.updatedTime,
            book.id];
        this.db.run(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                resolve(this.changes); //影响的记录数
            }
        })
    })
}

/**
 * 删除书籍
 * @param {Number} bookId 书籍ID
 * @returns {Promise}
 */
BookDB.prototype.remove = function(bookId){
    return new Promise((resolve,reject) => {
        let sql = "delete from books where id=?";
        let params = [bookId];
        this.db.run(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                resolve(this.changes);//影响的记录数
            }
        })
    })
}
/** 
* 获取书籍总数
* @returns {Promise}
*/
BookDB.prototype.getCount = function(){
    return new Promise((resolve,reject) => {
        let sql = "select count(*) from books";
        let params = [];
        this.db.get(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                util.log(result)
                resolve(result);//记录总数
            }
        })
    })
}

/**
 * 获取书籍信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns {Promise}
 */
BookDB.prototype.search = function(q,limit = -1,offset = -1){
    return new Promise((resolve,reject) => {
        let sql = "";
        q = `%${q}%`
        let params = [q,q];
        if(limit === -1){
            sql = "select * from books where title like ? or author like ?";
        }else if(offset === -1){
            sql = "select * from books title like ? or author like ? limit ?";
            params.push(limit);
        }else{
            sql = "select * from books title like ? or author like ? limit ? offset ?";
            params.push(limit);
            params.push(offset);
        }
        // 一条用get，所有用all
        this.db.all(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                resolve(result);
            }
        })
    })
}


/**
 * 通过isbn查找书籍
 * @returns {Promise}
 */
BookDB.prototype.findByIsbn = function(bookIsbn){
    return new Promise((resolve,reject) => {
        let sql = "select * from books where isbn = ?";
        let params = [bookIsbn]
        // 一条用get，所有用all
        this.db.all(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                resolve(result);
            }
        })
    })
}
module.exports = BookDB;